﻿using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using ContractManagermentSystem.Entity;

namespace ContractManagermentSystem.DataAccess
{
    public class ContractDao
    {
        public static void ImportCSV(string path)
        {
            //CSV File
            //ContractTypeId
            //ConsultantId
            //ClientId
            //Title
            //CreateDate
            //StartDate
            //EndDate
            //Value
            //Description
            //ContractorFullName
            //ContracorPhoneNumber1
            //ContracorPhoneNumber2
            //Gender
            //Branch
            //SortCode
            var dataTable = ParseCSV(path);
            var full = Path.GetFullPath(path);
            var file = Path.GetFileName(full);
            foreach (DataRow dataRow in dataTable.Rows)
            {
                var contract = new Contract
                                   {
                                       ContractTypeId = dataRow["F1"].ToString(),
                                       ConsultantId = dataRow["F2"].ToString(),
                                       ClientId = dataRow["F3"].ToString(),
                                       Title = dataRow["F4"].ToString(),
                                       Group = file,
                                       CreateDate = DateTime.Parse(dataRow["F5"].ToString()),
                                       StartDate = DateTime.Parse(dataRow["F6"].ToString()),
                                       EndDate = DateTime.Parse(dataRow["F7"].ToString()),
                                       Value = Double.Parse(dataRow["F8"].ToString()),
                                       Description = dataRow["F9"].ToString()
                                   };
                var contractor = new Contractor
                                     {
                                         FullName = dataRow["F10"].ToString(),
                                         PhoneNumber1 = dataRow["F11"].ToString(),
                                         PhoneNumber2 =
                                             string.IsNullOrEmpty(dataRow["F12"].ToString())
                                                 ? "Null"
                                                 : dataRow["F12"].ToString(),
                                         Gender = dataRow["F13"].ToString(),
                                         Branch = dataRow["F14"].ToString(),
                                         SortCode = dataRow["F15"].ToString()
                                     };
                //Save to database - the contract and contractor
            }
        }

        public static DataTable ParseCSV(string path)
        {
            if (!File.Exists(path))
                return null;

            var full = Path.GetFullPath(path);
            var file = Path.GetFileName(full);
            var dir = Path.GetDirectoryName(full);

            var connString = "Provider=Microsoft.Jet.OLEDB.4.0;"
              + "Data Source=\"" + dir + "\\\";"
              + "Extended Properties=\"text;HDR=No;FMT=Delimited\"";

            var query = "SELECT * FROM " + file;

            var dTable = new DataTable();

            var dAdapter = new OleDbDataAdapter(query, connString) { AcceptChangesDuringFill = true };
            try
            {
                //fill the DataTable
                dAdapter.Fill(dTable);
            }

            catch (InvalidOperationException /*e*/)
            { }
            dAdapter.Update(dTable.Select());
            dAdapter.Dispose();
            return dTable;
        }
    }
}
